package DBHandle;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;

import com.opensymphony.xwork2.Action;
import com.opensymphony.xwork2.ActionContext;

//添加销售数据
public class EditInputData
{
	//变量定义
	private InputStream inputStream;
	DBConn.DBConnManager db=new DBConn.DBConnManager();
	DBHandle.General dbHandle=new DBHandle.General();
	Statement stmt=null;
	ResultSet rs=null;
	String sql="";	
	Connection conn=db.getConnection("Mysql");
	
	public InputStream getInputStream() {
		return this.inputStream;
	}
	
	//获取VM中销售得物品列表
	@SuppressWarnings("deprecation")
	public String SelectVMItem() throws Exception
	{
		String[] venueCode=(String[])ActionContext.getContext().getParameters().get("venueCode");
		if(conn!=null)
		{
			sql="select distinct A.item from qbx_product A,qbx_aisles B,qbx_vm C where A.id = B.product_id and B.vm_id = C.id and C.venue_code = '"+venueCode[0]+"'";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			String item="";
			while(rs.next())
			{
				item+="<option>"+rs.getString("item")+"</option>";
			}
			inputStream=new StringBufferInputStream(item);
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		return Action.SUCCESS;
	}
	
	//获取VM中aisles信息
	@SuppressWarnings("deprecation")
	public String SelectVMAisles() throws Exception
	{
		String[] venueCode=(String[])ActionContext.getContext().getParameters().get("venueCode");
		if(conn!=null)
		{
			sql="select distinct A.aisles from qbx_aisles A,qbx_vm B where A.vm_id = B.id and B.venue_code = '"+venueCode[0]+"'";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			String item="<option></option>";
			while(rs.next())
			{
				item+="<option>"+rs.getString("aisles")+"</option>";
			}
			inputStream=new StringBufferInputStream(item);
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		return Action.SUCCESS;
	}
	
	//获取aisles中商品信息
	@SuppressWarnings("deprecation")
	public String SelectAislesItem() throws Exception
	{
		String[] number=(String[])ActionContext.getContext().getParameters().get("number");
		if(conn!=null)
		{
			sql="select B.item from qbx_aisles A,qbx_product B where A.product_id = B.id and A.aisles = '"+number[0]+"'";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			String item="";
			while(rs.next())
			{
				item+=rs.getString("item");
			}
			inputStream=new StringBufferInputStream(item);
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		return Action.SUCCESS;
	}
	
	//保存Discarded信息
	@SuppressWarnings("deprecation")
	public String SaveDiscard() throws Exception
    {
    	String[] venue=(String[])ActionContext.getContext().getParameters().get("venue");
    	String[] date=(String[])ActionContext.getContext().getParameters().get("date");
    	String[] item=(String[])ActionContext.getContext().getParameters().get("item");
    	String[] quantity=(String[])ActionContext.getContext().getParameters().get("quantity");
    	String[] reason=(String[])ActionContext.getContext().getParameters().get("reason");
    	sql="insert into qbx_discard(venuecode,item,quantity,reason,date) values('"+
    		venue[0]+"','"+item[0]+"',"+quantity[0]+",'"+reason[0]+"','"+date[0]+"')";
    	String sql1="update qbx_product set discarded = discarded+"+Integer.parseInt(quantity[0])+", remaining = remaining-"
    				+Integer.parseInt(quantity[0])+" where item = '"+item[0]+"'";

    	//请求数据库连接
		if(conn!=null)
		{
			stmt=conn.createStatement();
			int i=stmt.executeUpdate(sql);			
			int j=stmt.executeUpdate(sql1);
			stmt.close();
			if(i+j==2)
				inputStream=new StringBufferInputStream("true");
			else 
				inputStream=new StringBufferInputStream("false");		
			db.releaseConnection("Mysql", conn);
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
	    return Action.SUCCESS;			
  
    }
	
	//保存Sales信息
	public String SaveSales() throws Exception
    {
    	String[] venue=(String[])ActionContext.getContext().getParameters().get("venue");
    	String[] date=(String[])ActionContext.getContext().getParameters().get("date");
    	String[] time=(String[])ActionContext.getContext().getParameters().get("time");
    	String[] number=(String[])ActionContext.getContext().getParameters().get("number");
    	String[] item=(String[])ActionContext.getContext().getParameters().get("item");
    	String[] sales=(String[])ActionContext.getContext().getParameters().get("sales");
    	String[] status=(String[])ActionContext.getContext().getParameters().get("status");
    	sql="insert into qbx_sales(venuecode,date,time,aisles,item,sales,status) values('"+
    		venue[0]+"','"+date[0]+"','"+time[0]+"','"+number[0]+"','"+item[0]+"','"+sales[0]+"','"+status[0]+"')";
    	String sql1="select c.item from qbx_aisles a,qbx_vm b,qbx_product c where a.aisles = '"+number[0]
    	           +"' and a.vm_id = b.id and b.venue_code = '"+venue[0]+"' and c.id = a.product_id";
    	String sql2="update qbx_product set sold=sold+1,remaining=remaining-1 where item = '";
    	//请求数据库连接
		if(conn!=null)
		{
			stmt=conn.createStatement();
			int i=stmt.executeUpdate(sql);
			rs=stmt.executeQuery(sql1);
			while(rs.next())
			{
				sql2+=rs.getString("item")+"'";				
			}
			int j=stmt.executeUpdate(sql2);
			if(i+j==2)
				inputStream=new StringBufferInputStream("true");
			else 
				inputStream=new StringBufferInputStream("false");
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
        return Action.SUCCESS;  
    }
	
	
	//查询销售信息
	@SuppressWarnings("deprecation")
	public String SearchSales() throws Exception
	{
		if(conn==null)
		{
			inputStream = new StringBufferInputStream("error");
			return Action.SUCCESS;
		}
		String[] month=(String[])ActionContext.getContext().getParameters().get("month");
    	String[] remain=(String[])ActionContext.getContext().getParameters().get("remain");
    	String[] venueCode=(String[])ActionContext.getContext().getParameters().get("venueCode");
		String returnString="{"+GetSalesInfo(month[0]+"-1", 7, venueCode[0],"week1")+",";
		returnString+=GetSalesInfo(month[0]+"-8", 7, venueCode[0],"week2")+",";
		returnString+=GetSalesInfo(month[0]+"-15", 7, venueCode[0],"week3")+",";
		returnString+=GetSalesInfo(month[0]+"-22", 7, venueCode[0],"week4")+",";
		returnString+=GetSalesInfo(month[0]+"-29", 3, venueCode[0],"remainders")+",";
		returnString+=GetProblem(venueCode[0],month[0])+"}";
		inputStream=new StringBufferInputStream(returnString);
		db.releaseConnection("Mysql", conn);
		return Action.SUCCESS;
	}
	
	//获取销售信息
	private String GetSalesInfo(String startDay,int days,String venueCode,String name) throws Exception
	{	
    	stmt=conn.createStatement();
    	String[][] aislesStrings=new String[days][];
    	String[][] salesStrings=new String[days][];
    	for(int i=0;i<days;i++)
    	{
    		sql="select aisles,sales from qbx_sales where venuecode = '"+venueCode+"' and date = date_add('"+startDay+"',INTERVAL "+i+" DAY) order by aisles";		
    		rs=stmt.executeQuery(sql);
    		rs.last();
    		aislesStrings[i]=new String[rs.getRow()];
    		salesStrings[i]=new String[rs.getRow()];
    		rs.beforeFirst();
    		int j=0;
    		while(rs.next())
    		{
    			aislesStrings[i][j]=rs.getString("aisles");
    			salesStrings[i][j]=rs.getString("sales");
    			j++;
    		}	
    	}
    	rs.close();
    	stmt.close();
		
    	
    	int maxLength=0;
    	int weekQty=0;
    	float weekAmt=0;
    	String sumString="'"+name+"Sum':[";
    	for(int i=0;i<days;i++)
    	{
    		
    		float amt=0;
    		for(int j=0;j<salesStrings[i].length;j++)
    		{
    			amt+=Float.parseFloat(salesStrings[i][j]);
    		}
    		weekQty+=aislesStrings[i].length;
    		weekAmt+=amt;
    		sumString+="{qty:"+String.valueOf(aislesStrings[i].length)+",amt:"+String.valueOf(amt)+"},";
    		if(aislesStrings[i].length>maxLength)
    			maxLength=aislesStrings[i].length;
    	}
    	if(sumString.substring(sumString.length()-1).equals(","))
    		sumString=sumString.substring(0,sumString.length()-1);
    	sumString+="]";
    	String searchInfo="'"+name+"SearchInfo':'";
    	for(int j=0;j<maxLength;j++)
    	{    		
    		searchInfo+="<tr>";
    		for(int i=0;i<7;i++)
    		{
    			if(j>=aislesStrings[i].length)
    				searchInfo+="<td><td>";
    			else
    				searchInfo+="<td>"+aislesStrings[i][j]+"<td>"+salesStrings[i][j];
    		}   	
    	}
    	searchInfo+="'";
    	String returnString="'"+name+"TotalQty':'"+String.valueOf(weekQty)+"','"+name+"TotalAmt':'"+String.valueOf(weekAmt)+"',"+sumString+","+searchInfo;
    	return returnString;
	}
	
	//获取丢失商品信息
	private String GetProblem(String venueCode,String month) throws Exception
	{
		String returnString="'discard':[";
		stmt=conn.createStatement();
		sql="select item,quantity,date from qbx_discard where venuecode = '"+venueCode+"' and date between '"+month+"-1' and '"+month+"-31'";
		rs=stmt.executeQuery(sql);
		while(rs.next())
		{
			returnString+="{item:'"+rs.getString("item")+"',qty:'"+rs.getString("quantity")+"',date:'"+rs.getString("date")+"'},";
		}
		if(returnString.substring(returnString.length()-1).equals(","))
			returnString=returnString.substring(0,returnString.length()-1);
		returnString+="]";
		return returnString;
	}
}